package com.qt.system.dao.impl;

import static org.apache.commons.lang.StringUtils.isNotBlank;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.stereotype.Repository;

import com.bamboo.framework.base.impl.BaseDAO;
import com.bamboo.framework.entity.PageInfo;
import com.bamboo.framework.exception.SysException;
import com.qt.system.dao.IUserDao;
import com.qt.system.entity.UserMVO;

@Repository
public class UserDaoImpl extends BaseDAO<UserMVO> implements IUserDao {
	private static Logger logger = LoggerFactory.getLogger(UserDaoImpl.class);

	@Override
	public UserMVO insert(final UserMVO entity) throws SysException {
		final StringBuilder sql = new StringBuilder();
	    sql.append("INSERT INTO user (username,password,nickname,state,create_time,update_time,sts) ");
        sql.append("VALUES (?,?,?,?,date_format(?,'%Y-%m-%d %H:%i:%s'),date_format(?,'%Y-%m-%d %H:%i:%s'),?)");
        try {
        	logger.info(sql.toString());
     		jdbcTemplate.update(
					new PreparedStatementCreator(){
                        public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
							int i = 0;
							java.sql.PreparedStatement ps = conn.prepareStatement(sql.toString()); 
							
							ps.setString(++i, StringUtils.trimToNull(entity.getUsername()));
							ps.setString(++i, StringUtils.trimToNull(entity.getPassword()));
							ps.setString(++i, StringUtils.trimToNull(entity.getNickname()));
							ps.setString(++i, StringUtils.trimToNull(entity.getState()));
							ps.setString(++i, StringUtils.trimToNull(entity.getCreateTime()));
							ps.setString(++i, StringUtils.trimToNull(entity.getUpdateTime()));
							ps.setString(++i, StringUtils.trimToNull(entity.getSts()));
							
							return ps;
						}
					});
        } catch (DataAccessException e) {
            logger.error("增加user错误：{}", e.getMessage());
            throw new SysException("10000", "增加user错误", e);
        }
        return entity;
	}

	@Override
	public UserMVO queryBean(UserMVO entity) throws SysException {
		StringBuffer sql = new StringBuffer();
        sql.append("SELECT user_id,username,password,nickname,state,date_format(create_time,'%Y-%m-%d %H:%i:%s')create_time,date_format(update_time,'%Y-%m-%d %H:%i:%s')update_time,sts ");
        sql.append("FROM user ");
        sql.append("WHERE 1=1 ");

        List<Object> params = new ArrayList<Object>();
        try {
			if (entity != null) {
				sql.append(" AND user_id=? ");
				params.add(entity.getUserId());
			} 
			logger.info(sql.toString() + "--" + params.toString());
            entity = jdbcTemplate.queryForObject(sql.toString(),
                    params.toArray(),
                    new BeanPropertyRowMapper<UserMVO>(UserMVO.class));
        } catch (EmptyResultDataAccessException e) {
        } catch (IncorrectResultSizeDataAccessException e) {
        } catch (DataAccessException e) {
            logger.error("查询user错误：{}", e.getMessage());
            throw new SysException("10000", "查询user错误", e);
        }
        return entity;
	}

	@Override
	public List<UserMVO> queryList(UserMVO entity) throws SysException {
		StringBuffer sql = new StringBuffer();
        sql.append("SELECT user_id,username,password,nickname,state,date_format(create_time,'%Y-%m-%d %H:%i:%s')create_time,date_format(update_time,'%Y-%m-%d %H:%i:%s')update_time,sts ");
        sql.append("FROM user ");
        sql.append("WHERE 1=1 ");

        List<UserMVO> resultList = null;
        List<Object> params = new ArrayList<Object>();
        try {
            if (entity != null) {
            	if (StringUtils.isNotBlank(entity.getUserId())) {
					sql.append(" AND user_id=?");
                	params.add(entity.getUserId());
				}
            	if (StringUtils.isNotBlank(entity.getUsername())) {
					sql.append(" AND username=?");
                	params.add(entity.getUsername());
				}
            	if (StringUtils.isNotBlank(entity.getPassword())) {
					sql.append(" AND password=?");
                	params.add(entity.getPassword());
				}
            	if (StringUtils.isNotBlank(entity.getNickname())) {
					sql.append(" AND nickname=?");
                	params.add(entity.getNickname());
				}

            	if (StringUtils.isNotBlank(entity.getState())) {
					sql.append(" AND state=?");
                	params.add(entity.getState());
				}
            	if (StringUtils.isNotBlank(entity.getSts())) {
					sql.append(" AND sts=?");
                	params.add(entity.getSts());
				}
            }
			logger.info(sql.toString() + "--" + params.toString());
            resultList = jdbcTemplate.query(sql.toString(),
                    params.toArray(),
                    new BeanPropertyRowMapper<UserMVO>(UserMVO.class));
        } catch (DataAccessException e) {
            logger.error("查询user错误：{}", e.getMessage());
            throw new SysException("10000", "查询user错误", e);
        }
        return resultList;
	}

	@Override
	public int update(UserMVO entity) throws SysException {
		int rowsAffected;
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE user SET ");
        List<Object> params = new ArrayList<Object>();
        try {
            if (entity.getUsername() != null) {
                sql.append("username=?,");
                params.add(entity.getUsername());
            }
            if (entity.getPassword() != null) {
                sql.append("password=?,");
                params.add(entity.getPassword());
            }
            if (entity.getNickname() != null) {
                sql.append("nickname=?,");
                params.add(entity.getNickname());
            }
            if (entity.getState() != null) {
                sql.append("state=?,");
                params.add(entity.getState());
            }
            if (entity.getCreateTime() != null) {
                sql.append("create_time=date_format(?,'%Y-%m-%d %H:%i:%s'),");
                params.add(entity.getCreateTime());
            }
            if (entity.getUpdateTime() != null) {
                sql.append("update_time=date_format(?,'%Y-%m-%d %H:%i:%s'),");
                params.add(entity.getUpdateTime());
            }
            if (entity.getSts() != null) {
                sql.append("sts=?,");
                params.add(entity.getSts());
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(" WHERE user_id=?");
            params.add(entity.getUserId());
			logger.info(sql.toString() + "--" + params.toString());
            rowsAffected = jdbcTemplate.update(sql.toString(), params.toArray());
        } catch (DataAccessException e) {
            
            logger.error("更新user错误：{}", e.getMessage());
            throw new SysException("10000", "更新user错误", e);
        }
        return rowsAffected;
	}

	@Override
	public int delete(UserMVO entity) throws SysException {
		int rowsAffected;
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM user WHERE user_id=?");

        try {
        	logger.info(sql.toString());
            rowsAffected = jdbcTemplate.update(sql.toString(),
                   entity.getUserId());
        } catch (DataAccessException e) {
            logger.error("删除user错误：{}", e.getMessage());
            throw new SysException("10000", "删除user错误", e);
        }
        return rowsAffected;
	}

	@Override
	public PageInfo queryPage(UserMVO entity, PageInfo pageInfo) throws SysException {
		StringBuffer sql = new StringBuffer();
        sql.append("select user_id,username,password,nickname,state,date_format(create_time,'%Y-%m-%d %H:%i:%s')create_time,date_format(update_time,'%Y-%m-%d %H:%i:%s')update_time,sts ");
        sql.append("from user ");
        sql.append("where 1=1");

        List<Object> params = new ArrayList<Object>();
        try {
            if (entity != null) {
            	if (StringUtils.isNotBlank(entity.getUserId())) {
					sql.append(" AND user_id=?");
                	params.add(entity.getUserId());
				}
            	if (StringUtils.isNotBlank(entity.getUsername())) {
					sql.append(" AND username=?");
                	params.add(entity.getUsername());
				}
            	if (StringUtils.isNotBlank(entity.getPassword())) {
					sql.append(" AND password=?");
                	params.add(entity.getPassword());
				}
            	if (StringUtils.isNotBlank(entity.getNickname())) {
					sql.append(" AND nickname=?");
                	params.add(entity.getNickname());
				}

            	if (StringUtils.isNotBlank(entity.getState())) {
					sql.append(" AND state=?");
                	params.add(entity.getState());
				}
            	if (StringUtils.isNotBlank(entity.getSts())) {
					sql.append(" AND sts=?");
                	params.add(entity.getSts());
				}
            }
            pageInfo = this.pagingQuery(sql.toString(), pageInfo, params,
                    new BeanPropertyRowMapper<UserMVO>(UserMVO.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
            logger.error("查询user错误：{}", e.getMessage());
            throw new SysException("10000", "查询user错误", e);
        }
        return pageInfo;
	}

	@Override
	public UserMVO login(UserMVO entity) throws SysException {
		StringBuffer sql = new StringBuffer();
        sql.append("SELECT user_id,username,password,nickname,state,date_format(create_time,'%Y-%m-%d %H:%i:%s')create_time,date_format(update_time,'%Y-%m-%d %H:%i:%s')update_time,sts ");
        sql.append("FROM user ");
        sql.append("WHERE 1=1 ");

        List<UserMVO> resultList = null;
        List<Object> params = new ArrayList<Object>();
        try {
			if (entity != null) {
				sql.append(" AND username=? ");
				params.add(entity.getUsername());
			} 
			logger.info(sql.toString() + "--" + params.toString());
			resultList = jdbcTemplate.query(sql.toString(),
                    params.toArray(),
                    new BeanPropertyRowMapper<UserMVO>(UserMVO.class));
			if (resultList == null || resultList.size() <= 0) {
				return null;
			}
        } catch (EmptyResultDataAccessException e) {
        } catch (IncorrectResultSizeDataAccessException e) {
        } catch (DataAccessException e) {
            logger.error("查询user错误：{}", e.getMessage());
            throw new SysException("10000", "查询user错误", e);
        }
        return resultList.get(0);
	}
}
